package com.poker.jkiuus.uyrfgd;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.poker.iuious.yhyhjs.jklimj.ChatBean;
import com.poker.iuious.yhyhjs.jklimj.User;

import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "ChatDatabase.db";

    private static final String TABLE_NAME = "chat_history";
    private static final String COLUMN_USER_ID = "user_id";
    private static final String COLUMN_MESSAGE = "message";
    private static final String COLUMN_STATE = "state";
    private static final String  CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (id INTEGER PRIMARY KEY AUTOINCREMENT, "
            + COLUMN_USER_ID + " TEXT, "
            + COLUMN_MESSAGE + " TEXT, "
            + COLUMN_STATE + " INTEGER)";

    public static final String TABLE_NAME_USER = "table_user";
    private static final String COLUMN_NAME = "name";
    private static final String COLUMN_PASSWORD = "password";
    private static final String COLUMN_PHONE = "phone";
    private static final String COLUMN_EMAIL = "email";
    private static final String COLUMN_REL_NAME = "relName";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_GENDER = "gender";
    private static final String COLUMN_LOGIN_TIME = "login_time";
    private static final String COLUMN_LOGIN_id = "login_id";
    private static final String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_NAME_USER + "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_NAME + " TEXT, "
            + COLUMN_PASSWORD + " TEXT, "
            + COLUMN_EMAIL + " TEXT, "
            + COLUMN_PHONE + " TEXT, "
            + COLUMN_LOGIN_TIME + " TEXT, "
            + COLUMN_GENDER + " INTEGER, "
            + COLUMN_REL_NAME + " TEXT, "
            + COLUMN_LOGIN_id + " TEXT)";

    private static final String CREATE_TABLE_QUERIES = "CREATE TABLE UserQueries (id INTEGER PRIMARY KEY AUTOINCREMENT,userId INTEGER,queries TEXT,date TEXT)";
    private static final String TABLE_NAME_FEED = "CREATE TABLE feedback (id INTEGER PRIMARY KEY AUTOINCREMENT,feedbackType TEXT,feedbackContent TEXT)";

    private static final String firstSql = "INSERT INTO table_user(name,password,email,phone,gender,relName,login_id) VALUES('testUser','2e36e8c73c392f59fbb1e4001985edd9','test@qq.com','',1,'','Ai')";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(CREATE_TABLE);
        db.execSQL(CREATE_TABLE_USER);
        db.execSQL(CREATE_TABLE_QUERIES);
        db.execSQL(TABLE_NAME_FEED);
        db.execSQL(firstSql);


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void updateLoginTime(int userId, String loginTime) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(COLUMN_LOGIN_TIME, loginTime);

        db.update(TABLE_NAME_USER, values, "id = ?", new String[] { String.valueOf(userId) });
        db.close();
    }


    public boolean insertUserQuery(int userId, String query, String date) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("userId", userId);
        contentValues.put("queries", query);
        contentValues.put("date", date);
        long result = db.insert("UserQueries", null, contentValues);
        return result != -1;
    }

    @SuppressLint("Range")
    public List<String> getQueriesByDate(int userId, String date) {
        List<String> queries = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query("UserQueries", new String[] {"queries"}, "userId = ? AND date = ?", new String[] {String.valueOf(userId), date}, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                queries.add(cursor.getString(cursor.getColumnIndex("queries")));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return queries;
    }

    @SuppressLint("Range")
    public List<String> getDistinctDates(int userId) {
        List<String> dates = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(true, "UserQueries", new String[] {"date"}, "userId = ?", new String[] {String.valueOf(userId)}, null, null, "date DESC", null);
        if (cursor.moveToFirst()) {
            do {
                dates.add(cursor.getString(cursor.getColumnIndex("date")));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return dates;
    }



    public void saveChatHistory(String userId, String message, int state) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COLUMN_USER_ID, userId);
        contentValues.put(COLUMN_MESSAGE, message);
        contentValues.put(COLUMN_STATE, state);
        db.insert(TABLE_NAME, null, contentValues);
    }


    @SuppressLint("Range")
    public List<ChatBean> getChatHistory(String userId) {
        List<ChatBean> chatHistory = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_USER_ID + "=?", new String[]{userId});
        while (cursor.moveToNext()) {
            ChatBean chatBean = new ChatBean();
            chatBean.setMessage(cursor.getString(cursor.getColumnIndex(COLUMN_MESSAGE)));
            chatBean.setState(cursor.getInt(cursor.getColumnIndex(COLUMN_STATE)));
            chatHistory.add(chatBean);
        }
        cursor.close();
        return chatHistory;
    }


    public void clearChatHistory(String userId) {
        SQLiteDatabase db = this.getWritableDatabase();

        db.delete(TABLE_NAME, COLUMN_USER_ID + " = ?", new String[]{userId});
        db.close();
    }


    public void clearUserQueries(int userId) {
        SQLiteDatabase db = this.getWritableDatabase();

        db.delete("UserQueries", "userId = ?", new String[]{String.valueOf(userId)});
        db.close();
    }


    public boolean insertData(User user) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put("name", user.getName());
        values.put("password", user.getPassword());
        values.put("email", user.getEmail());
        long userId = db.insert(TABLE_NAME_USER, null, values);


        @SuppressLint("DefaultLocale") String loginId = "Ai " + String.format("%06d", userId);


        ContentValues updateValues = new ContentValues();
        updateValues.put(COLUMN_LOGIN_id, loginId);
        db.update(TABLE_NAME_USER, updateValues, COLUMN_ID + " = ?", new String[] { String.valueOf(userId) });

        db.close();


        return userId > 0;

    }

    public boolean remove(String name) {
        SQLiteDatabase db = getReadableDatabase();
        return db.delete(TABLE_NAME_USER, "name = ?", new String[]{name}) > 0;
    }

    public boolean updateData(User user) {

        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put("name", user.getName());
        values.put("email", user.getEmail());
        values.put("phone", user.getPhone());
        values.put("gender", user.getGender());
        values.put("relName", user.getRelName());

        return db.update(TABLE_NAME_USER, values, "name = ?", new String[]{user.getName()}) > 0;

    }

    public boolean updateDataPws(User user) {

        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put("name", user.getName());
        values.put("password", user.getPassword());

        return db.update(TABLE_NAME_USER, values, "name = ?", new String[]{user.getName()}) > 0;

    }

    public int updatePasswordByUserName(String name, String newPassword) {
        SQLiteDatabase db = getReadableDatabase();
        ContentValues values = new ContentValues();

        values.put("password", newPassword);
        return db.update(TABLE_NAME_USER, values, "name = ?", new String[]{name});

    }

    public boolean queryUserByName(String username) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME_USER, null, "name = ?", new String[]{username}, null, null, null);

        boolean userExists = cursor.moveToFirst();
        cursor.close();
        return userExists;
    }

    public boolean queryUserByPhone(String phone) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor=db.query(TABLE_NAME_USER,null,"phone = ?",new String[]{phone},null,null,null);

        boolean userExists = cursor.moveToFirst();
        cursor.close();
        return userExists;
    }

    public boolean queryUserByEmail(String email) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor=db.query(TABLE_NAME_USER,null,"email = ?",new String[]{email},null,null,null);

        boolean userExists = cursor.moveToFirst();
        cursor.close();
        return userExists;
    }

    public User selectUserByPhone(String phone) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor=db.query(TABLE_NAME_USER,null,"phone = ?",new String[]{phone},null,null,null);

        User user = null;

        if (cursor != null && cursor.moveToFirst()) {

            String userName = cursor.getString(cursor.getColumnIndexOrThrow("name"));
            String userPassword = cursor.getString(cursor.getColumnIndexOrThrow("password"));
            String userPhone = cursor.getString(cursor.getColumnIndexOrThrow("phone"));
            String userEmail = cursor.getString(cursor.getColumnIndexOrThrow("email"));
            String userLoginId = cursor.getString(cursor.getColumnIndexOrThrow("login_id"));
            String userLoginTime = cursor.getString(cursor.getColumnIndexOrThrow("login_time"));
            Integer userGender = cursor.getInt(cursor.getColumnIndexOrThrow("gender"));
            String userRelName = cursor.getString(cursor.getColumnIndexOrThrow("relName"));
            user = new User(userName, userPassword, userPhone,userEmail,userLoginId,userLoginTime,userGender,userRelName);

            user.setId(cursor.getInt(cursor.getColumnIndexOrThrow("id")));

        }

        if (cursor != null) {
            cursor.close();
        }

        return user;
    }

    public User selectUserByName(String name) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor=db.query(TABLE_NAME_USER,null,"name = ?",new String[]{name},null,null,null);

        User user = null;

        if (cursor != null && cursor.moveToFirst()) {

            String userName = cursor.getString(cursor.getColumnIndexOrThrow("name"));
            String userPassword = cursor.getString(cursor.getColumnIndexOrThrow("password"));
            String userPhone = cursor.getString(cursor.getColumnIndexOrThrow("phone"));
            String userEmail = cursor.getString(cursor.getColumnIndexOrThrow("email"));
            String userLoginId = cursor.getString(cursor.getColumnIndexOrThrow("login_id"));
            String userLoginTime = cursor.getString(cursor.getColumnIndexOrThrow("login_time"));
            Integer userGender = cursor.getInt(cursor.getColumnIndexOrThrow("gender"));
            String userRelName = cursor.getString(cursor.getColumnIndexOrThrow("relName"));
            user = new User(userName, userPassword, userPhone,userEmail,userLoginId,userLoginTime,userGender,userRelName);

            user.setId(cursor.getInt(cursor.getColumnIndexOrThrow("id")));

        }

        if (cursor != null) {
            cursor.close();
        }

        return user;
    }

    public boolean login(User user) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME_USER, null, "(name = ? or email = ?) and password = ?", new String[]{user.getName(), user.getName(), user.getPassword()}, null, null, null);

        boolean userExists = cursor.moveToFirst();
        cursor.close();
        return userExists;
    }

    public User queryUserByNameOrEmail(String name) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME_USER, null, "name = ? or email = ?", new String[]{name, name}, null, null, null);

        User user = null;

        if (cursor != null && cursor.moveToFirst()) {

            String userName = cursor.getString(cursor.getColumnIndexOrThrow("name"));
            String userRelName = cursor.getString(cursor.getColumnIndexOrThrow("relName"));
            String userPassword = cursor.getString(cursor.getColumnIndexOrThrow("password"));
            String userPhone = cursor.getString(cursor.getColumnIndexOrThrow("phone"));
            String userEmail = cursor.getString(cursor.getColumnIndexOrThrow("email"));
            String userLoginId = cursor.getString(cursor.getColumnIndexOrThrow("login_id"));
            String userLoginTime = cursor.getString(cursor.getColumnIndexOrThrow("login_time"));
            Integer userGender = cursor.getInt(cursor.getColumnIndexOrThrow("gender"));

            user = new User(userName, userPassword, userPhone,userEmail,userLoginId,userLoginTime,userGender,userRelName);

            user.setId(cursor.getInt(cursor.getColumnIndexOrThrow("id")));

        }

        if (cursor != null) {
            cursor.close();
        }

        return user;
    }

    public boolean insertFeedback(String feedbackType, String feedbackContent) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("feedbackType", feedbackType);
        contentValues.put("feedbackContent", feedbackContent);
        return db.insert("feedback", null, contentValues) > 0;
    }

    public boolean isUserValid(String userName, String phone, String email) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor query = db.query(TABLE_NAME_USER, null, "name = ? and phone = ? and email = ?", new String[]{userName, phone, email}, null, null, null);

        boolean b = query.moveToFirst();
        query.close();

        return b;
    }

    public User selectUserByEmail(String email) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor=db.query(TABLE_NAME_USER,null,"email = ?",new String[]{email},null,null,null);

        User user = null;

        if (cursor != null && cursor.moveToFirst()) {

            String userName = cursor.getString(cursor.getColumnIndexOrThrow("name"));
            String userPassword = cursor.getString(cursor.getColumnIndexOrThrow("password"));
            String userPhone = cursor.getString(cursor.getColumnIndexOrThrow("phone"));
            String userEmail = cursor.getString(cursor.getColumnIndexOrThrow("email"));
            String userLoginId = cursor.getString(cursor.getColumnIndexOrThrow("login_id"));
            String userLoginTime = cursor.getString(cursor.getColumnIndexOrThrow("login_time"));
            Integer userGender = cursor.getInt(cursor.getColumnIndexOrThrow("gender"));
            String userRelName = cursor.getString(cursor.getColumnIndexOrThrow("relName"));

            user = new User(userName, userPassword, userPhone,userEmail,userLoginId,userLoginTime,userGender,userRelName);

            user.setId(cursor.getInt(cursor.getColumnIndexOrThrow("id")));

        }

        if (cursor != null) {
            cursor.close();
        }

        return user;

    }
}
